/*

package com.own.demo.lwf.utils.workUtils;
package com.own.demo.workUtils;

import cn.hsa.cep.plc.std.common.adapt.dict.entity.DataDictDO;
import cn.hsa.cep.plc.std.common.adapt.dict.entity.TaDictCache;
import cn.hsa.mbs.common.exception.MbsException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.ReflectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


@Slf4j
public class FileImportExportUtil {


*/
/**
     * 公用xls 文件转换成 list （不允许中间空行）
     *
     * @param is           文件输入流
     * @param fieldNames   属性名
     * @param dtoClassPath 对应DTO类全路径
     * @param row          需要跳过的行数(包括标题行)
     * @return excel中获取的list
     * @author caiww
     * @date 2019/10/11
     *//*



    public static List excelToList(InputStream is, String fieldNames, String dtoClassPath, int row) throws Exception {
        List retList = new ArrayList();
        String[] fieldName = fieldNames.split(",");
        int errorRow = 1;
        try {
            WorkbookSettings workbookSettings = new WorkbookSettings();
            Workbook book = Workbook.getWorkbook(is, workbookSettings);
            Sheet sheet = book.getSheet(0);
            String cresult = "";
            Object tmpObj = null;
            for (int nn = row; nn < sheet.getRows(); nn++) {
                errorRow = nn + 1;
                String[] values = new String[fieldName.length];
                for (int mm = 0; mm < fieldName.length; mm++) {
                    Cell cell1 = sheet.getCell(mm, nn);
                    cresult = cell1.getContents();
                    values[mm] = cresult.trim();
                }
                try {
                    tmpObj = generatorObjectFromArray(fieldName, values, dtoClassPath);
                    int valuesLength = values.length;
                    boolean flag = false;
                    for (int a = 0; a < valuesLength; a++) {
                        if (!ValidateUtil.isEmpty(values[a])) {
                            flag = true;
                            break;
                        }
                    }
                    if (flag) {
                        retList.add(tmpObj);
                    } else {
                        break;
                    }

                } catch (Exception e) {
                    throw new MbsException("在第" + (nn + 1) + "行数据格式不符合要求，请检查！" + e.getMessage());
                }
            }
        } catch (Exception e) {
            throw new MbsException("数据导入出错！" + e.getMessage() + "出错行：" + errorRow);
        }
        return retList;
    }


*/
/**
     *  公用xlsx 文件转换成 list （不允许中间空行）
     * @param is
     * @param fieldNames
     * @param dtoClassPath
     * @param row
     * @return java.util.List
     * @author liubiao
     * @created 2020-02-20 16:04
     *//*


    public static List excelToList4xlsx(InputStream is, String fieldNames, String dtoClassPath, int row) throws Exception {
        List retList = new ArrayList();
        String[] fieldName = fieldNames.split(",");
        int errorRow = 1;
        String[] values;
        FileInputStream fileInputStream=null;
        if(is instanceof FileInputStream){
            fileInputStream=(FileInputStream)is;
        }
        try {
            org.apache.poi.ss.usermodel.Workbook book = new XSSFWorkbook(fileInputStream);
            org.apache.poi.ss.usermodel.Sheet sheet = book.getSheetAt(0);
            String cresult = "";
            Object tmpObj = null;
            CellAddress cellAddress;
            for (int nn = row; nn < sheet.getLastRowNum()+1; nn++) {
                errorRow = nn + 1;
                values = new String[fieldName.length];
                for (int mm = 0; mm < fieldName.length; mm++) {
                    cellAddress=new CellAddress(mm,nn);
                    Row row1 = sheet.getRow(nn);
                    cresult = row1.getCell(mm)==null?"":row1.getCell(mm).getStringCellValue();
                    values[mm] = cresult.trim();
                }
                try {
                    tmpObj = generatorObjectFromArray(fieldName, values, dtoClassPath);
                    int valuesLength = values.length;
                    boolean flag = false;
                    for (int a = 0; a < valuesLength; a++) {
                        if (!ValidateUtil.isEmpty(values[a])) {
                            flag = true;
                            break;
                        }
                    }
                    if (flag) {
                        retList.add(tmpObj);
                    } else {
                        break;
                    }

                } catch (Exception e) {
                    throw new MbsException("在第" + (nn + 1) + "行数据格式不符合要求，请检查！" + e.getMessage());
                }
            }
        } catch (Exception e) {
            throw new MbsException("数据导入出错！" + e.getMessage() + "出错行：" + errorRow);
        }
        return retList;
    }


*/
/**
     * 根据属性名将excel行数据转换为对象
     *
     * @param fieldNames
     * @param values
     * @param dtoClassPath
     * @return
     * @throws java.lang.Exception
     *//*


    private static Object generatorObjectFromArray(String[] fieldNames, String[] values, String dtoClassPath) throws Exception {
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");
        SimpleDateFormat sdf3 = new SimpleDateFormat("yyyy/MM/dd");
        if (fieldNames.length != values.length) {
            throw new MbsException("属性数量与实际数据数量不符");
        }
        if (values.length == 0) {
            throw new MbsException("行数据为空，无法转换");
        }
        Class<?> aClass = Class.forName(dtoClassPath);
        Field[] declaredFields = aClass.getDeclaredFields();
        Object o = aClass.newInstance();
        for (Field field : declaredFields) {
            for (int i = 0; i < fieldNames.length; i++) {
                String filedName = fieldNames[i];
                if (ValidateUtil.areEqual(filedName, field.getName())) {
                    //field.setAccessible(true);
                    ReflectionUtils.makeAccessible(field);
                    if (ValidateUtil.isEmpty(values[i])) {
                        field.set(o, null);
                    } else {
                        if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.util.Date")) {
                            if(values[i].length()==8) {
                                field.set(o, sdf2.parse(values[i]));
                            }else if(values[i].length()==10){
                                try {
                                    field.set(o, sdf1.parse(values[i]));
                                } catch (ParseException e) {
                                    field.set(o, sdf3.parse(values[i]));
                                }
                            }else {
                                throw new MbsException("日期的格式有误，请检查");
                            }
                        } else if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.sql.Timestamp")) {
                            field.set(o, Timestamp.valueOf(values[i]));
                        } else if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.math.BigDecimal")) {
                            field.set(o, new BigDecimal(values[i]));
                        } else if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.lang.String")) {
                            field.set(o, values[i]);
                        } else if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.lang.Integer")) {
                            field.set(o, Integer.valueOf(values[i]));
                        } else if (ValidateUtil.areEqual(field.getGenericType().toString(), "class java.lang.Double")) {
                            field.set(o, Double.valueOf(values[i]));
                        }
                    }
                }
            }
        }
        return o;
    }

*/
/**
     * 导出数据
     *
     * @param headName   列名
     * @param headId     列ID
     * @param collection 码值
     * @param list       数据
     * @param fileName   文件名
     * @param response   响应
     * @throws java.lang.Exception
     *//*


    public static void exportExcel(List<String> headName, List<String> headId, List<DataDictDO> collection, List list,
                                   String fileName, HttpServletResponse response) throws Exception {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        //HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFWorkbook workbook = null;
        ServletOutputStream outputStream = null;
        try{
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("sheet1");
            HSSFRow rowHead = sheet.createRow(0);
            for (int j = 0; j < headName.size(); j++) {
                HSSFCell cell = rowHead.createCell(j);
                HSSFRichTextString text = null;
                if ("".equals(headName.get(j)) || headName.get(j) == null) {
                    text = new HSSFRichTextString("");
                } else {
                    text = new HSSFRichTextString(headName.get(j));
                }
                cell.setCellValue(text);
            }
            Object obj = null;
            Map map = new HashMap();
            for (int i = 1; i <= list.size(); i++) {
                HSSFRow row = sheet.createRow(i);
                obj = list.get(i - 1);
                map = objToMap(obj);
                for (int j = 0; j < headName.size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    HSSFRichTextString text = new HSSFRichTextString();
                    if ("".equals(map.get(headId.get(j))) || map.get(headId.get(j)) == null) {
                        text = new HSSFRichTextString("");
                    } else {
                        if (!ValidateUtil.isEmpty(collection)) {
                            for (DataDictDO dataDictDO : collection) {
                                if (dataDictDO.getName().equals(headName.get(j))) {
                                    if (map.get(headId.get(j)).equals(dataDictDO.getValue())) {
                                        text = new HSSFRichTextString(dataDictDO.getLabel());

                                    }
                                } else {
                                    text = new HSSFRichTextString(map.get(headId.get(j)).toString());
                                }
                            }
                        } else {
                            if("feeOcurTime".equals(headId.get(j))||"brdy".equals(headId.get(j))){
                                Date date = new Date(String.valueOf(map.get(headId.get(j))));
                                String a = simpleDateFormat.format(date);
                                text =new HSSFRichTextString(a);
                            }else {
                                text = new HSSFRichTextString(map.get(headId.get(j)).toString());
                            }
                        }
                    }
                   cell.setCellValue(text);
                }
            }
    //        response.setContentType("application/octet-stream");
    //        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setCharacterEncoding("UTF-8");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
            outputStream.flush();
            outputStream.close();
        }
        catch (Exception e){
            throw new MbsException(e.getMessage());
        }
        finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("workbook关闭错误");
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("outputStream关闭错误");
                }
            }
        }
    }

*/
/**
     * 导出模板
     *
     * @param headName 列名
     * @param fileName 文件名
     * @param response 响应
     * @param request
     * @throws java.lang.Exception
     *//*


    public static void exportExcelTemplate(List<String> headName, String fileName, HttpServletResponse response, HttpServletRequest request) throws Exception {
        //HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFWorkbook workbook = null;
        ServletOutputStream outputStream = null;
        try{
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("sheet1");
            HSSFRow rowHead = sheet.createRow(0);
            for (int j = 0; j < headName.size(); j++) {
                HSSFCell cell = rowHead.createCell(j);
                HSSFRichTextString text = null;
                if ("".equals(headName.get(j)) || headName.get(j) == null) {
                    text = new HSSFRichTextString("");
                } else {
                    text = new HSSFRichTextString(headName.get(j));
                }
                cell.setCellValue(text);
            }
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xls");

            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
            outputStream.flush();
            outputStream.close();
        }
        catch (Exception e){
            throw new MbsException(e.getMessage());
        }
        finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("workbook关闭错误");
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("outputStream关闭错误");
                }
            }
        }
    }

*/
/**
     * 对象转map
     *
     * @param obj 对象
     * @throws java.lang.Exception
     *//*



    public static Map<String, String> objToMap(Object obj) throws MbsException {
        Map<String, String> map = new HashMap<String, String>();
        // 获取f对象对应类中的所有属性域
        Field[] fields = obj.getClass().getDeclaredFields();
        for (int i = 0, len = fields.length; i < len; i++) {
            String varName = fields[i].getName();
            //将key置为小写，默认为对象的属性
//            varName = varName.toLowerCase();
            try {
                // 获取原来的访问控制权限
                boolean accessFlag = fields[i].isAccessible();
                // 修改访问控制权限
                ReflectionUtils.makeAccessible(fields[i]);
                // 获取在对象f中属性fields[i]对应的对象中的变量
                Object o = fields[i].get(obj);
                if (o != null) {
                    map.put(varName, o.toString());
                }
                // 恢复访问控制权限
               // fields[i].setAccessible(accessFlag);
            } catch (IllegalArgumentException | IllegalAccessException ex) {
                throw new MbsException(ex.getMessage());
            }
        }
        return map;
    }


    public static void exportXlsx(List<String> headName, List<String> headId, Map<String, TaDictCache> dicMap, List list, String fileName, HttpServletResponse response) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFWorkbook workbook = null;
        ServletOutputStream outputStream = null;
        try{
            workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("sheet1");
            XSSFRow rowHead = sheet.createRow(0);
            for (int j = 0; j < headName.size(); j++) {
                XSSFCell cell = rowHead.createCell(j);
                cell.setCellValue(headName.get(j));
                String headstrs[] = headId.get(j).split(":");
                if (headstrs.length > 1) {
                    List<String> labels = BaseUtil.getDictLabels(headstrs[1]);
                    if (labels != null && labels.size() > 0) {
                        addValidate(sheet, labels, headName.get(j), j);
                    }
                }
            }
            Object obj = null;
            Map map;
            for (int i = 1; i <= list.size(); i++) {
                XSSFRow row = sheet.createRow(i);
                obj = list.get(i - 1);
                map = FileImportExportUtil.objToMap(obj);
                for (int j = 0; j < headId.size(); j++) {
                    XSSFCell cell = row.createCell(j);
                    XSSFRichTextString text = null;
                    String[] headstrs = headId.get(j).split(":");
                    String head = null;
                    String dictName = null;
                    if (headstrs.length > 1) {
                        head = (String) map.get(headstrs[0]);
                        dictName = headstrs[1];
                    } else {
                        head = (String) map.get(headId.get(j));
                    }
                    if ("".equals(head) || head == null) {
                        text = new XSSFRichTextString("");
                    } else if ( dictName!=null && !dictName.equals("")) {
                        head = BaseUtil.getDict(dictName, head,dicMap);
                        text = new XSSFRichTextString(head);
                    } else {
                        Field field =  obj.getClass().getDeclaredField(headId.get(j));
                        boolean b = field.isAccessible();
                        field.setAccessible(true);
                        if( field.get(obj) instanceof Date){
                            String a = sdf.format(field.get(obj));
                            text = new XSSFRichTextString(a);
                        } else {
                            text = new XSSFRichTextString(head);
                        }
                        field.setAccessible(b);
                    }
                    cell.setCellValue(text);
                }
            }
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setCharacterEncoding("UTF-8");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            workbook.close();
            outputStream.flush();
            outputStream.close();
        }
        catch (Exception e){
            throw new MbsException(e.getMessage());
        }
        finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("workbook关闭错误");
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("outputStream关闭错误");
                }
            }
        }
    }

    private static void addValidate(XSSFSheet sheet, List<String> list, String head, int column) {
        String[] str = list.toArray(new String[list.size()]);
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(str);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 500, column, column);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setShowErrorBox(true);
        validation.createErrorBox("警告", head + "选择有误");
        sheet.addValidationData(validation);
    }
}

*/
